package tcc.wa.persistence.dao.base;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Base class for all other data access object classes.
 */
public abstract class DAOBase extends SQLiteOpenHelper {
	
	protected static final String LOCATION_TABLE_NAME = "location";
	
	protected static final String FORECAST_TABLE_NAME = "forecast";
	
	protected static final String CURRENT_WEATHER_CONDITION_TABLE_NAME = "currentweathercondition";
	
	protected static final String DAILY_WEATHER_FORECAST_TABLE_NAME = "dailyweatherforecast";
	
	protected static final String HOURLY_WEATHER_FORECAST_TABLE_NAME = "hourlyweatherforecast";
	
	protected static final String ALERT_TABLE_NAME = "alert";
	
	private static final String TAG = "DAOBase";
	
	private static final String DATABASE_NAME = "weatheralert.db";
	
	private static final int DATABASE_VERSION = 8;
	
	private static final String CREATE_LOCATION_TABLE = 
			"CREATE TABLE " + LOCATION_TABLE_NAME + "(id INTEGER PRIMARY KEY AUTOINCREMENT, city TEXT NOT NULL, state TEXT NOT NULL, " +
					"region TEXT, country TEXT NOT NULL, latitude REAL NOT NULL, longitude REAL NOT NULL);";
	
	private static final String CREATE_FORECAST_TABLE = 
			"CREATE TABLE " + FORECAST_TABLE_NAME + "(id INTEGER PRIMARY KEY AUTOINCREMENT, date INTEGER NOT NULL, " +
					"location_id INTEGER NOT NULL, FOREIGN KEY(location_id) REFERENCES location(id));";
	
	private static final String CREATE_CURRENT_WEATHER_CONDITION_TABLE = 
			"CREATE TABLE " + CURRENT_WEATHER_CONDITION_TABLE_NAME + "(id INTEGER PRIMARY KEY AUTOINCREMENT, forecast_id INTEGER NOT NULL, " +
					"maximum_temperature REAL NOT NULL, minimum_temperature REAL NOT NULL, current_temperature REAL NOT NULL, " +
					"relative_humidity REAL NOT NULL, precipitation REAL NOT NULL, FOREIGN KEY (forecast_id) REFERENCES forecast(id));";
	
	private static final String CREATE_DAILY_WEATHER_FORECAST_TABLE = 
			"CREATE TABLE " + DAILY_WEATHER_FORECAST_TABLE_NAME + "(id INTEGER PRIMARY KEY AUTOINCREMENT, forecast_id INTEGER NOT NULL, " +
					"relative_humidity REAL NOT NULL, precipitation REAL NOT NULL, maximum_temperature REAL NOT NULL, " +
					"minimum_temperature REAL NOT NULL, FOREIGN KEY (forecast_id) REFERENCES forecast(id));";
					
	private static final String CREATE_HOURLY_WEATHER_FORECAST_TABLE = 
			"CREATE TABLE " + HOURLY_WEATHER_FORECAST_TABLE_NAME + "(id INTEGER PRIMARY KEY AUTOINCREMENT, forecast_id INTEGER NOT NULL, " + 
					"relative_humidity REAL NOT NULL, precipitation REAL NOT NULL, temperature REAL NOT NULL, " +
					"FOREIGN KEY (forecast_id) REFERENCES forecast(id));";
	
	private static final String CREATE_ALERT_TABLE = 
			"CREATE TABLE " + ALERT_TABLE_NAME + "(id INTEGER PRIMARY KEY AUTOINCREMENT, type INTEGER NOT NULL, value REAL NOT NULL, " +
					"hours_advance INTEGER NOT NULL)";
	
	public DAOBase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	/* (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.w(TAG, "Creating database. Version: " + DATABASE_VERSION);
		db.execSQL(CREATE_LOCATION_TABLE);
		db.execSQL(CREATE_FORECAST_TABLE);
		db.execSQL(CREATE_CURRENT_WEATHER_CONDITION_TABLE);
		db.execSQL(CREATE_DAILY_WEATHER_FORECAST_TABLE);
		db.execSQL(CREATE_HOURLY_WEATHER_FORECAST_TABLE);
		db.execSQL(CREATE_ALERT_TABLE);
	}

	/* (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + LOCATION_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + FORECAST_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + CURRENT_WEATHER_CONDITION_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + DAILY_WEATHER_FORECAST_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + HOURLY_WEATHER_FORECAST_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + ALERT_TABLE_NAME);
		onCreate(db);
	}
}
